#!/usr/bin/env python3
"""
数据库初始化脚本
"""
import asyncio
from sqlalchemy import text
from core.database import engine, SessionLocal
from models import *
import structlog

logger = structlog.get_logger(__name__)


def create_tables():
    """创建所有数据表"""
    try:
        # 导入所有模型以确保它们被注册
        from models import (
            Camera, AITask, AIResult, Alert, User, SystemConfig, WVPDeviceStatus
        )
        
        # 创建所有表
        from models.base import BaseModel
        BaseModel.metadata.create_all(bind=engine)
        logger.info("数据库表创建成功")
        return True
    except Exception as e:
        logger.error("数据库表创建失败", exc_info=e)
        return False


def create_indexes():
    """创建索引"""
    try:
        with engine.connect() as conn:
            # 摄像头表索引
            conn.execute(text("CREATE INDEX IF NOT EXISTS idx_cameras_status ON cameras(status)"))
            conn.execute(text("CREATE INDEX IF NOT EXISTS idx_cameras_location ON cameras(latitude, longitude)"))
            
            # AI任务表索引
            conn.execute(text("CREATE INDEX IF NOT EXISTS idx_ai_tasks_status_priority ON ai_tasks(status, priority)"))
            conn.execute(text("CREATE INDEX IF NOT EXISTS idx_ai_tasks_camera ON ai_tasks(camera_id)"))
            
            # AI结果表索引
            conn.execute(text("CREATE INDEX IF NOT EXISTS idx_ai_results_timestamp ON ai_results(timestamp)"))
            conn.execute(text("CREATE INDEX IF NOT EXISTS idx_ai_results_camera ON ai_results(camera_id)"))
            
            # 告警表索引
            conn.execute(text("CREATE INDEX IF NOT EXISTS idx_alerts_status_created ON alerts(status, created_at)"))
            conn.execute(text("CREATE INDEX IF NOT EXISTS idx_alerts_camera_type ON alerts(camera_id, alert_type)"))
            
            # 用户表索引
            conn.execute(text("CREATE INDEX IF NOT EXISTS idx_users_username ON users(username)"))
            conn.execute(text("CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)"))
            
            # 系统配置表索引
            conn.execute(text("CREATE INDEX IF NOT EXISTS idx_system_configs_key ON system_configs(config_key)"))
            conn.execute(text("CREATE INDEX IF NOT EXISTS idx_system_configs_category ON system_configs(category)"))
            
            conn.commit()
            logger.info("数据库索引创建成功")
            return True
    except Exception as e:
        logger.error("数据库索引创建失败", exc_info=e)
        return False


def create_initial_data():
    """创建初始数据"""
    try:
        db = SessionLocal()
        
        # 创建默认管理员用户
        admin_user = db.query(User).filter(User.username == "admin").first()
        if not admin_user:
            from passlib.context import CryptContext
            pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")
            
            admin_user = User(
                username="admin",
                password_hash=pwd_context.hash("admin123"),
                email="admin@campus-twin.com",
                full_name="系统管理员",
                role="admin",
                is_active=True
            )
            db.add(admin_user)
            logger.info("创建默认管理员用户")
        
        # 创建默认系统配置
        default_configs = [
            {
                "config_key": "system.name",
                "config_value": "校园视频孪生系统",
                "config_type": "string",
                "description": "系统名称",
                "is_public": True,
                "category": "system"
            },
            {
                "config_key": "system.version",
                "config_value": "1.0.0",
                "config_type": "string",
                "description": "系统版本",
                "is_public": True,
                "category": "system"
            },
            {
                "config_key": "ai.max_concurrent_tasks",
                "config_value": "10",
                "config_type": "integer",
                "description": "AI最大并发任务数",
                "is_public": False,
                "category": "ai"
            },
            {
                "config_key": "alert.retention_days",
                "config_value": "30",
                "config_type": "integer",
                "description": "告警保留天数",
                "is_public": False,
                "category": "alert"
            },
            # WVP相关配置
            {
                "config_key": "wvp.server_url",
                "config_value": "http://localhost:18080",
                "config_type": "string",
                "description": "WVP-Pro服务器地址",
                "is_public": False,
                "category": "wvp"
            },
            {
                "config_key": "wvp.username",
                "config_value": "admin",
                "config_type": "string",
                "description": "WVP-Pro用户名",
                "is_public": False,
                "category": "wvp"
            },
            {
                "config_key": "wvp.password",
                "config_value": "admin",
                "config_type": "string",
                "description": "WVP-Pro密码",
                "is_public": False,
                "category": "wvp"
            },
            {
                "config_key": "wvp.sync_interval",
                "config_value": "300",
                "config_type": "integer",
                "description": "WVP设备同步间隔(秒)",
                "is_public": False,
                "category": "wvp"
            },
            {
                "config_key": "wvp.auto_sync_enabled",
                "config_value": "true",
                "config_type": "boolean",
                "description": "是否启用自动同步WVP设备",
                "is_public": False,
                "category": "wvp"
            }
        ]
        
        for config_data in default_configs:
            existing_config = db.query(SystemConfig).filter(
                SystemConfig.config_key == config_data["config_key"]
            ).first()
            
            if not existing_config:
                config = SystemConfig(**config_data)
                db.add(config)
                logger.info(f"创建系统配置: {config_data['config_key']}")
        
        # 创建示例摄像头数据（包含WVP相关字段）
        sample_cameras = [
            {
                "name": "教学楼A-101",
                "location": "教学楼A栋1楼101教室",
                "latitude": 39.904200,
                "longitude": 116.407396,
                "height": 15.0,
                "rtsp_url": "rtsp://192.168.1.100:554/stream1",
                "wvp_device_id": "34020000001320000001",
                "wvp_channel_id": "34020000001320000001",
                "device_info": {
                    "manufacturer": "海康威视",
                    "model": "DS-2CD2T47G1-L",
                    "firmware": "V5.6.3",
                    "ip": "192.168.1.100",
                    "port": 554
                },
                "status": "online",
                "ai_enabled": True,
                "ai_algorithms": {
                    "person_detection": True,
                    "vehicle_detection": True,
                    "face_recognition": False
                }
            },
            {
                "name": "教学楼A-102", 
                "location": "教学楼A栋1楼102教室",
                "latitude": 39.904300,
                "longitude": 116.407500,
                "height": 15.0,
                "rtsp_url": "rtsp://192.168.1.101:554/stream1",
                "wvp_device_id": "34020000001320000002",
                "wvp_channel_id": "34020000001320000002",
                "device_info": {
                    "manufacturer": "大华",
                    "model": "DH-IPC-HFW4433M-I2",
                    "firmware": "V2.800.0000000.25.R",
                    "ip": "192.168.1.101",
                    "port": 554
                },
                "status": "online",
                "ai_enabled": True,
                "ai_algorithms": {
                    "person_detection": True,
                    "vehicle_detection": False,
                    "face_recognition": True
                }
            },
            {
                "name": "操场东侧",
                "location": "学校操场东侧",
                "latitude": 39.905000,
                "longitude": 116.408000,
                "height": 8.0,
                "rtsp_url": "rtsp://192.168.1.102:554/stream1",
                "wvp_device_id": "34020000001320000003",
                "wvp_channel_id": "34020000001320000003",
                "device_info": {
                    "manufacturer": "宇视",
                    "model": "IPC6322SR-X22P-VF",
                    "firmware": "V1.0.0",
                    "ip": "192.168.1.102",
                    "port": 554
                },
                "status": "offline",
                "ai_enabled": False,
                "ai_algorithms": {
                    "person_detection": False,
                    "vehicle_detection": False,
                    "face_recognition": False
                }
            }
        ]
        
        for camera_data in sample_cameras:
            existing_camera = db.query(Camera).filter(
                Camera.name == camera_data["name"]
            ).first()
            
            if not existing_camera:
                camera = Camera(**camera_data)
                db.add(camera)
                logger.info(f"创建示例摄像头: {camera_data['name']}")
        
        # 创建WVP设备状态数据
        from models.wvp_device_status import WVPDeviceStatus
        from datetime import datetime
        
        wvp_devices = [
            {
                "device_id": "34020000001320000001",
                "device_name": "教学楼A-101摄像头",
                "status": "online",
                "ip_address": "192.168.1.100",
                "port": 5060,
                "manufacturer": "海康威视",
                "model": "DS-2CD2T47G1-L",
                "firmware": "V5.6.3",
                "last_keepalive": datetime.now(),
                "sync_time": datetime.now(),
                "channel_count": 1,
                "online_channel_count": 1,
                "longitude": "116.407396",
                "latitude": "39.904200"
            },
            {
                "device_id": "34020000001320000002",
                "device_name": "教学楼A-102摄像头",
                "status": "online",
                "ip_address": "192.168.1.101",
                "port": 5060,
                "manufacturer": "大华",
                "model": "DH-IPC-HFW4433M-I2",
                "firmware": "V2.800.0000000.25.R",
                "last_keepalive": datetime.now(),
                "sync_time": datetime.now(),
                "channel_count": 1,
                "online_channel_count": 1,
                "longitude": "116.407500",
                "latitude": "39.904300"
            },
            {
                "device_id": "34020000001320000003",
                "device_name": "操场东侧摄像头",
                "status": "offline",
                "ip_address": "192.168.1.102",
                "port": 5060,
                "manufacturer": "宇视",
                "model": "IPC6322SR-X22P-VF",
                "firmware": "V1.0.0",
                "sync_time": datetime.now(),
                "channel_count": 1,
                "online_channel_count": 0,
                "longitude": "116.408000",
                "latitude": "39.905000"
            }
        ]
        
        for wvp_data in wvp_devices:
            existing_wvp = db.query(WVPDeviceStatus).filter(
                WVPDeviceStatus.device_id == wvp_data["device_id"]
            ).first()
            
            if not existing_wvp:
                wvp_device = WVPDeviceStatus(**wvp_data)
                db.add(wvp_device)
                logger.info(f"创建WVP设备状态: {wvp_data['device_name']}")
        
        db.commit()
        db.close()
        logger.info("初始数据创建完成")
        return True
        
    except Exception as e:
        logger.error("初始数据创建失败", exc_info=e)
        if 'db' in locals():
            db.rollback()
            db.close()
        return False


def main():
    """主函数"""
    logger.info("开始初始化数据库...")
    
    # 创建表
    if not create_tables():
        logger.error("数据库表创建失败，退出")
        return False
    
    # 创建索引
    if not create_indexes():
        logger.error("数据库索引创建失败，退出")
        return False
    
    # 创建初始数据
    if not create_initial_data():
        logger.error("初始数据创建失败，退出")
        return False
    
    logger.info("数据库初始化完成！")
    return True


if __name__ == "__main__":
    success = main()
    exit(0 if success else 1)